package dao;

import modle.CompetitionInformation;
import modle.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class CompetitionDao {
    private Connection connection = null;
    public List<CompetitionInformation> getSearchCompetitionInformation() {
        List<CompetitionInformation> list = new ArrayList<>();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        StringBuilder sql = new StringBuilder("select * from v_competition_information WHERE 1=1 ORDER BY `CI_TIME` ASC, `SI_ID` ASC, `G_ID` ASC;");//参数占位符

        ResultSet rs = null;
        try{
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            rs = pstmt.executeQuery();
            int num = 1;
            while (rs.next()) {
                Calendar cal = Calendar.getInstance();
                // 获取时间戳信息
                Timestamp rtimestamp = rs.getTimestamp("CI_Time");
                Timestamp rnewTimestamp = null;

                cal.setTimeInMillis(rtimestamp.getTime());
                // 对Calendar对象进行处理，减少8个小时
                cal.add(Calendar.HOUR_OF_DAY, -8);
                rnewTimestamp = new java.sql.Timestamp(cal.getTime().getTime());

                CompetitionInformation competitionInformation = new CompetitionInformation();
                competitionInformation.setId(rs.getInt("CI_ID"));
                competitionInformation.setNum(num++);
                competitionInformation.setTime(rnewTimestamp);
                competitionInformation.setVenue(rs.getString("CI_Venue"));
                competitionInformation.setItemId(rs.getInt("SI_ID"));
                competitionInformation.setItemName(rs.getString("SI_Name"));
                competitionInformation.setCategory(rs.getInt("SI_Category"));
                competitionInformation.setNature(rs.getInt("CI_Nature"));
                competitionInformation.setLimit(rs.getInt("CI_Limit"));
                competitionInformation.setGroup(rs.getInt("G_ID"));
                competitionInformation.setGroupName(rs.getString("G_NAME"));

                list.add(competitionInformation);
            }
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
        }
        catch (NullPointerException e){
            return null;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return list;
    }

    public String insertCompetitionInformation(String venue,String date,int s_id,int limit,int nature,int g_id){
        String message = "";
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_INSERT_CompetitionInformation(?, ?, ?, ?, ?, ?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setString(1, venue);
            pstmt.setString(2, date);
            pstmt.setInt(3, s_id);
            pstmt.setInt(4, limit);
            pstmt.setInt(5, nature);
            pstmt.setInt(6, g_id);
            pstmt.execute();
            pstmt.close();
            connection.close();
            message = "OK";
        }catch (SQLException e) {
            message = e.getMessage();
        }finally {

        }
        return message;
    }
    public String updateCompetitionInformation(int ci_id,String venue,String date,int s_id,int limit,int nature,int g_id){
        String message = "";
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_Update_CompetitionInformation(?, ?, ?, ?, ?, ?, ?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, ci_id);
            pstmt.setString(2, venue);
            pstmt.setString(3, date);
            pstmt.setInt(4, s_id);
            pstmt.setInt(5, limit);
            pstmt.setInt(6, nature);
            pstmt.setInt(7, g_id);
            pstmt.execute();
            pstmt.close();
            connection.close();
            message = "OK";
        }catch (SQLException e) {
            message = e.getMessage();
        }finally {

        }
        return message;
    }
}
